package com.rest.common.utils

import com.rest.common.Exception.CommonServerException
import org.apache.poi.hssf.usermodel.HSSFCell
import org.apache.poi.hssf.usermodel.HSSFDateUtil
import org.apache.poi.ss.usermodel.Cell
import org.apache.poi.xssf.usermodel.XSSFCell
import org.apache.poi.xssf.usermodel.XSSFRow
import org.apache.poi.xssf.usermodel.XSSFSheet
import org.apache.poi.xssf.usermodel.XSSFWorkbook
import org.slf4j.Logger
import org.slf4j.LoggerFactory
import org.springframework.web.multipart.MultipartFile
import org.springframework.web.multipart.MultipartHttpServletRequest
import java.lang.reflect.Field
import java.text.SimpleDateFormat
import javax.servlet.http.HttpServletRequest

object ExcelUtils {
    val logger: Logger = LoggerFactory.getLogger(ExcelUtils::class.java)
    /**
     * 根据request请求获取XSSFWorkbook对象
     */
    fun getWorkBookByRequest(request: HttpServletRequest): XSSFWorkbook?{
        val multipartRequest: MultipartHttpServletRequest = request as MultipartHttpServletRequest
        val item:Iterator<String> =  multipartRequest.fileNames
        var workBook: XSSFWorkbook? = null
        while (item.hasNext()){
            val fileName:String = item.next()
            val files:List<MultipartFile> = multipartRequest.getFiles(fileName)
            files
            val file: MultipartFile = files[0]
            val fileExt:String = file.originalFilename.substring(file.originalFilename.lastIndexOf(".") + 1).toLowerCase()
            if (!fileExt.endsWith("xlsx")){
                throw CommonServerException("上传文件格式只能是xlsx")
            }
            workBook = XSSFWorkbook(file.inputStream)
            break
        }
        return workBook
    }

    /**
     * 根据model获取excel数据
     * excel说明:
     * 1、模板第一行为标题行，标题名称必须与model属性名称一致，顺序不限、个数不限
     * 2、model的Int导入是在excel中使用字符串
     */
    fun <T:Any> getExcelData(workBook:XSSFWorkbook,model:T):MutableList<T>{
        var dataList:MutableList<T> = arrayListOf()
        var modelProperties:MutableList<String> = arrayListOf()
        var clazz = model::class.java
        var fieldArray:Array<Field> = clazz.declaredFields
        fieldArray.forEach {
            modelProperties.add(it.name)
        }
        var titleIndexMap:MutableMap<String,Int> = hashMapOf()
        val sheet: XSSFSheet = workBook.getSheetAt(0)
        val titleRow : XSSFRow = sheet.getRow(0)
        var index:Int = 0
        logger.info("init title row....")
        while (true) {
            if (this.isBlankCell(titleRow,index)) {
                break
            }
            val cell = titleRow.getCell(index)
            val cellValue = cell.stringCellValue
            val cellIndex = cell.columnIndex
            if(modelProperties.contains(cellValue)){
                titleIndexMap.put(cellValue, cellIndex)
            }
            index++
        }
        logger.info("title size="+titleIndexMap.size)
        var beginRow:Int = 2
        while (true){
            logger.info("begin load excel data...")
            var row:XSSFRow? = sheet.getRow(beginRow)
            if (null == row || this.isBlankCell(row,0) && this.isBlankCell(row,1)) {
                break
            }
            var entity = clazz.newInstance()
            for((K,V) in titleIndexMap){
                try {
                    var field = clazz.getDeclaredField(K)
                    field.isAccessible = true
                    val cellvalue:String = getValue(row.getCell(V))
                    var typeClass:Class<*> = field.type
                    var fieldValue:Any? = when {
                        typeClass.name.endsWith("int") -> cellvalue.toIntOrNull()
                        typeClass.name.endsWith("Boolean") -> cellvalue.toBoolean()
                        else -> cellvalue
                    }
                    field.set(entity,fieldValue)
                }catch (e:Exception){
                    logger.error("parse field error:"+e)
                    continue
                }
            }
            dataList.add(entity)
            logger.info("excel row :"+beginRow)
            beginRow++
        }
        logger.info("data size: "+dataList.size)
        return dataList
    }


    fun getValue(xssfCell: XSSFCell): String {
        xssfCell
        return when (xssfCell.cellType){
            Cell.CELL_TYPE_BOOLEAN -> xssfCell.booleanCellValue.toString()
            Cell.CELL_TYPE_NUMERIC -> {
                if (HSSFDateUtil.isCellDateFormatted(xssfCell)) {
                    SimpleDateFormat("yyyy-MM-dd").format(xssfCell.dateCellValue)
                }
                xssfCell.numericCellValue.toString()
            }
            else -> return if (!xssfCell.stringCellValue.isNullOrEmpty()) {
                xssfCell.stringCellValue.toString()
            } else {
                ""
            }
        }
    }

    /**
     * 判断excel单元格是否为空
     */
    private fun isBlankCell(xssfRow:XSSFRow,index:Int):Boolean{
        if (null == xssfRow.getCell(index) || (xssfRow.getCell(index).cellType == HSSFCell.CELL_TYPE_BLANK)){
            return true
        }
        return false
    }

}